﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MaxLeaf.Platform.Contract;
using MaxLeaf.Platform.DataContract;
using CommonsLib.DB;
using System.Transactions;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;
using CommonsLib.Settings;
using System.Data;

using System.Security.Cryptography;
using MaxLeaf.Platform.Functions;
using MaxLeaf.Platform.Commons;
using CommonsLib.Utility;

namespace MaxLeaf.Platform.DALImplementation
{
    public class ImInvLocDAL :BaseDAL 
    {

        protected override string GetCondForPage(DBAccess dbcon, object model, List<Cond> lstCond)
        {
            string strCond = base.GetCondForPage(dbcon, model, lstCond);
            
            foreach (Cond cond in lstCond)
            {
                if (cond.PropName.Equals("Func_CheckGoodsCategoryName"))
                {
                    if (!string.IsNullOrEmpty(strCond))
                    {
                        strCond += " and ";
                    }
                    strCond += string.Format(@"  exists (select a.cat_id from {0}goods_category a,{0}iminvmaster i where i.finterid=iminvloc.itemid 
                                            and a.cat_name like '{1}' 
                                            and {0}F_ExistInGoodsCategory(i.GoodsID,a.cat_id)=1) "
                        ,GetBaseDatabase()
                        ,BaseFunc.SpecialCharDeal(CvtFunc.ToString( cond.Value))
                        );
                    continue;
                }
                
            }
            return strCond;
        }

        internal static string InvLocQryTableName(object model)
        {                      
            string strSql =string.Format (@"
                (
                    select  invLoc.*
                    ,isnull(catTop.cat_id,0) as GoodsCategoryTopId,isnull(catTop.cat_name,'') as GoodsCategoryTopName
                    ,isnull(catCur.cat_id,0) as GoodsCategoryCurId,isnull(catCur.cat_name,'') as GoodsCategoryCurName
                    ,isnull(scpTop.finterid,0) as SCPCategoryTopId,isnull(scpTop.SCPCategoryName,'') as SCPCategoryTopName
                    ,isnull(scpCur.finterid,0) as SCPCategoryCurId,isnull(scpCur.SCPCategoryName,'') as SCPCategoryCurName
                    ,g.Goods_id as GoodsId,g.goods_name as GoodsName,g.sku_num

                    ,isnull(g_uk.usegoods_name,'') as usegoods_name,isnull(g_uk.usegoods_id,0) as usegoods_id
                    ,isnull(g_b.brand_id,0) as brand_id,isnull(g_b.brand_name,'') as brand_name

                    , isnull({0}F_CheckContractItem(i.goodsid,i.stockunitid,{1},{2}),0)  as Is_ContractItem

                    from iminvloc invloc 
                    inner join {0}ImInvMaster i on i.finterid=invloc.itemid and i.is_delete=0  and i.Is_UsedAttr=1
                    inner join {0}goods_main g on i.goodsid=g.goods_id  and g.is_delete=0 and g.is_on_sale=1
                    left join {0}goods_brand g_b on g_b.brand_id=g.brand_id
                    left join {0}goods_category catTop on  catTop.cat_id={0}F_GetGoodsCategoryID(i.goodsid,1)
                    left join {0}goods_category catCur on  catCur.cat_id={0}F_GetGoodsCategoryID(i.goodsid,0)
                    left join {0}SCPCategory scpTop on scpTop.CompanyID=invloc.Companyid and  scpTop.finterid={0}F_GetSCPCategoryID(invloc.Companyid,i.goodsid,1)
                    left join {0}SCPCategory scpCur on scpCur.CompanyID=invloc.Companyid and  scpCur.finterid={0}F_GetSCPCategoryID(invloc.Companyid,i.goodsid,0)
                    left join {0}goods_UserKeywords as g_uk on g_uk.Company_ID=invloc.Companyid and  g_uk.Itemid=i.finterid
                    
                ) a"
                , GetBaseDatabase()
                     , DataFunc.GetPropValue(model, "UI_UserID")
                    , DataFunc.GetPropValue(model, "UI_AreaID")
                    );

            return strSql;    
        
        }


        public static double GetOnOrder_Req(DBAccess dbOrder, int intCompanyID, int intItemFinterID, int intFromUnitID)
        {
            //try
            //{
                string strSql =string.Format(@"select sum({0}F_UnitCnv(i.goodsid,d.reqqty,@ToUnit,d.unitid))  from poReqDtl d inner join poReqHdr h on d.hdrid=h.finterid  
                inner join {0}ImInvMaster i on i.finterid=d.itemid
                where (case when h.OrigCompanyID = 0 then h.CompanyID else h.OrigCompanyID end)=@CompanyID
                and d.itemid=@itemid  
                and h.posted=0 and h.Is_Delete=0
                and d.posted=0 and d.Is_Delete=0 and d.reqqty>0
                ", GetBaseDatabase()
                    );
                return CvtFunc.ToDouble(dbOrder.GetValue(strSql,new SqlParameter[]{
                    new SqlParameter("@CompanyID",intCompanyID.ToString())
                    ,new SqlParameter("@itemid",intItemFinterID.ToString())
                    ,new SqlParameter("@ToUnit",intFromUnitID.ToString())
                }));
            //}
            //catch (Exception ex)
            //{
            //    LogManager.Error(ex);

            //    return 0;
            //}
        }

        public static double GetComm_Req(DBAccess dbOrder, int intCompanyID, int intItemFinterID, int intFromUnitID)
        {
            //try
            //{
                string strSql = string.Format(@"select sum({0}F_UnitCnv(i.goodsid,d.reqqty-d.outputqty,@ToUnit,d.unitid)) 
                from imStoresOutputDtl d inner join imStoresOutputHdr h on d.hdrid=h.finterid  
                inner join {0}ImInvMaster i on i.finterid=d.itemid
                where  h.CompanyID =@CompanyID
                and d.itemid=@itemid
                and h.posted=0 and h.Is_Delete=0
                and d.posted=0 and d.Is_Delete=0 and d.reqqty-d.outputqty>0
                ", GetBaseDatabase()
                    );
                return CvtFunc.ToDouble(dbOrder.GetValue(strSql, new SqlParameter[]{
                    new SqlParameter("@CompanyID",intCompanyID.ToString())
                    ,new SqlParameter("@itemid",intItemFinterID.ToString())
                    ,new SqlParameter("@ToUnit",intFromUnitID.ToString())
                }));
            //}
            //catch (Exception ex)
            //{
            //    LogManager.Error(ex);

            //    return 0;
            //}
        }

        public static double GetOnOrder_Order(DBAccess dbOrder, int intCompanyID, int intItemFinterID, int intFromUnitID)
        {
            string strSql = string.Format(@"select sum({0}F_UnitCnv(i.goodsid,d.reqqty,@ToUnit,d.unitid))  from poOrderDtl d inner join poOrderHdr h on d.hdrid=h.finterid  
                inner join {0}ImInvMaster i on i.finterid=d.itemid
                where (case when h.OrigCompanyID = 0 then h.CompanyID else h.OrigCompanyID end)=@CompanyID
                and d.itemid=@itemid  
                and h.posted=0 and h.Is_Delete=0
                and d.posted=0 and d.Is_Delete=0 and d.reqqty>0
                ", GetBaseDatabase()
                    );
            return CvtFunc.ToDouble(dbOrder.GetValue(strSql, new SqlParameter[]{
                    new SqlParameter("@CompanyID",intCompanyID.ToString())
                    ,new SqlParameter("@itemid",intItemFinterID.ToString())
                    ,new SqlParameter("@ToUnit",intFromUnitID.ToString())
                }));
        }

        public static double GetOnOrder_Recv(DBAccess dbOrder, int intCompanyID, int intItemFinterID, int intFromUnitID)
        {
            string strSql = string.Format(@"select sum({0}F_UnitCnv(i.goodsid,d.reqqty-d.recvqty,@ToUnit,d.unitid)) 
                from poOrderRecvDtl d inner join poOrderRecvhdr h on d.hdrid=h.finterid  
                inner join {0}ImInvMaster i on i.finterid=d.itemid
                where  h.CompanyID =@CompanyID
                and d.itemid=@itemid
                and h.posted=0 and h.Is_Delete=0
                and d.posted=0 and d.Is_Delete=0 and d.reqqty-d.recvqty>0
                ", GetBaseDatabase()
                    );
            return CvtFunc.ToDouble(dbOrder.GetValue(strSql, new SqlParameter[]{
                    new SqlParameter("@CompanyID",intCompanyID.ToString())
                    ,new SqlParameter("@itemid",intItemFinterID.ToString())
                    ,new SqlParameter("@ToUnit",intFromUnitID.ToString())
                }));
        }

        public static double GetOnOrder_Others(DBAccess dbOrder, int intCompanyID, int intItemFinterID, int intFromUnitID)
        {
            string strSql = string.Format(@"select sum({0}F_UnitCnv(i.goodsid,d.reqqty,@ToUnit,d.unitid)) 
                from imOthersInputDtl d inner join imOthersInputhdr h on d.hdrid=h.finterid  
                inner join {0}ImInvMaster i on i.finterid=d.itemid
                where  h.CompanyID =@CompanyID
                and d.itemid=@itemid
                and h.posted=0 and h.Is_Delete=0
                and d.posted=0 and d.Is_Delete=0 and d.reqqty>0
                ", GetBaseDatabase()
                    );
            return CvtFunc.ToDouble(dbOrder.GetValue(strSql, new SqlParameter[]{
                    new SqlParameter("@CompanyID",intCompanyID.ToString())
                    ,new SqlParameter("@itemid",intItemFinterID.ToString())
                    ,new SqlParameter("@ToUnit",intFromUnitID.ToString())
                }));
        }

        public static double GetComm_Others(DBAccess dbOrder, int intCompanyID, int intItemFinterID, int intFromUnitID)
        {
            string strSql = string.Format(@"select sum({0}F_UnitCnv(i.goodsid,d.reqqty-d.outputqty,@ToUnit,d.unitid)) 
                from imOthersOutputDtl d inner join imOthersOutputHdr h on d.hdrid=h.finterid  
                inner join {0}ImInvMaster i on i.finterid=d.itemid
                where  h.CompanyID =@CompanyID
                and d.itemid=@itemid
                and h.posted=0 and h.Is_Delete=0
                and d.posted=0 and d.Is_Delete=0 and d.reqqty-d.outputqty>0
                ", GetBaseDatabase()
                    );
            return CvtFunc.ToDouble(dbOrder.GetValue(strSql, new SqlParameter[]{
                    new SqlParameter("@CompanyID",intCompanyID.ToString())
                    ,new SqlParameter("@itemid",intItemFinterID.ToString())
                    ,new SqlParameter("@ToUnit",intFromUnitID.ToString())
                }));
        }
    }
}
